<?php
/**
 * 记录每一个库存的库存值表：存放一个库存的尺码方面的信息
 * String id：ID
 * String sku：唯一sku
 * String size：尺码
 * String value：理论库存
 * String t_value：实际库存
 * String warning_value：警告阀值
 */
class App_Model_StockValue extends App_Model_Abstract
{
	public function init()
	{
		parent::init();
		$this->table = App_Db_Table::factory('StockValue');
	}
	
	public function add($row)
	{
		return $this->table->insert($row);
	}
	
	public function getById($id)
	{
		$row = $this->table->find($id);
		if ($row) {
			return $row->current();
		}
		return array();
	}
	
	public function getOne($id)
	{
		if(!empty($id)){
		    $sql1 = 'select v.id, v.sku, v.size, v.value, v.t_value, s.title, s.is_forlong from stock_value v inner join stock s on s.sku=v.sku where v.id="'.$id.'"';
		    $res1 = $this->table->getAdapter()->fetchAll($sql1);
		    return $res1;
		}else{
			return array();
		}
	}
	
	public function update($row, $id)
	{
		$db = $this->table->getAdapter();
		$where = $db->quoteInto('id = ?', $id);
		
		return $this->table->update($row, $where);
	}
	
	public function delete($sku)
	{
		$db = $this->table->getAdapter();
		$where = $db->quoteInto('sku = ?', $sku);
		
		$res = $this->table->delete($where);
		return $res;
	}

	public function deleteById($id)
	{
		$db = $this->table->getAdapter();
		$where = $db->quoteInto('id = ?', $id);
	
		$res = $this->table->delete($where);
		return $res;
	}
	
	public function getStockValue($fieldArray)
	{
		$select = $this->table->select();
		if($fieldArray!=null){
			foreach ($fieldArray as $field => $value){
				$select->where("$field = ?",$value);
			}
		}
		return $this->table->fetchAll($select);
	}
	
	/**获取理论值大于实际值*/
	public function getUnusualValue()
	{
		$sql1 = 'select v.id, v.sku, v.size, v.value, v.t_value, s.title, s.is_forlong from stock_value v inner join stock s on s.sku=v.sku where v.value > v.t_value order by sku';
		$res1 = $this->table->getAdapter()->fetchAll($sql1);
		$sql2 = 'select v.id, v.sku, v.size, v.value, v.t_value, s.title, s.is_forlong from stock_value v inner join stock s on s.sku=v.sku where v.t_value < 0 order by sku';
		$res2 = $this->table->getAdapter()->fetchAll($sql2);
		return array_merge($res1,$res2);
	}

	/**获取异常库存的列表*/
	public function listUnusual($page=1,$sku,$limit=10)
	{
		if(!empty($sku)){
			$sql = "select v.id, v.sku, v.size, v.value, v.t_value, s.title, s.is_forlong, s.stock_id from stock_value v inner join stock s on s.sku=v.sku where v.sku='$sku' and (v.value > v.t_value or v.t_value < 0)";
			$res = $this->table->getAdapter()->fetchAll($sql);
		}else{
		    $db = $this->table->getAdapter();
		    $select = $db->select();
			$offset = ($page - 1) * $limit;
			$select->from('stock_value', array('id','sku','size','value','t_value'));
			$select->where('value > t_value');
			$select->orWhere('t_value < 0');
			$select->order('sku');
			$select->limit($limit,$offset);
			$select->join('stock', 'stock.sku = stock_value.sku', array('title','is_forlong','stock_id'));//多表联合查询
			$res = $db->fetchAll($select);
		}
		
		return $res;
	}
	
	/**获取异常库存的数量*/
	public function getTotalUnusualNum()
	{
		$where="where 1 ";
		$sql = 'select count(*) from stock_value v inner join stock s on s.sku=v.sku where v.value > v.t_value or v.t_value < 0';
		return $this->table->getAdapter()->fetchOne($sql);
	}

	/**库存量盘点，存在于给定的尺码id集*/
	public function inventoryExit($sizeid)
	{
		$db = $this->table->getAdapter();
		$select = $db->select();
		
		$select->from('stock_value', array('id','sku','size','value','t_value'));
		foreach ($sizeid as $k=>$v){
			$select->orWhere('id = ?',$k);
		}
		$select->order('sku');
		$select->join('stock', 'stock.sku = stock_value.sku', array('title','is_forlong','stock_id'));//多表联合查询
		return $db->fetchAll($select);
	}
	
	/**库存量盘点，不存在于给定的尺码id集*/
	public function inventoryMiss($sizeid)
	{
		$db = $this->table->getAdapter();
		$select = $db->select();
	
		$select->from('stock_value', array('id','sku','size','value','t_value'));
		foreach ($sizeid as $k=>$v){
			$select->Where('id <> ?',$k);
		}
		$select->order('sku');
		$select->join('stock', 'stock.sku = stock_value.sku', array('title','is_forlong'));//多表联合查询
		$select->where('is_forlong = 0');//取出永不下架为否的那些系统库存存在而盘点时不存在的
		return $db->fetchAll($select);
	}
	
	public function getBySku($sku)
	{
		$db= $this->table->getAdapter();
		$where = $db->quoteInto('sku in (?)',$sku);
		$rows = $this->table->fetchAll($where,'id asc');
		return $rows;
	}
	
	/**增加理论库存*/
	public function addnum($sku,$size,$value)
	{
		$db = $this->table->getAdapter();
		$sql = "update stock_value set value=value+$value where sku='$sku' and size='$size'";
		$result = $db->query($sql);
		return $result;
	}

	/**减少理论库存*/
	public function minusnum($sku,$size,$value)
	{
		$db = $this->table->getAdapter();
		$sql = "update stock_value set value=value-$value where sku='$sku' and size='$size'";
		$result = $db->query($sql);
		return $result;
	}

	/**增加实际库存*/
	public function addtnum($sku,$size,$value)
	{
		$db = $this->table->getAdapter();
		$sql = "update stock_value set t_value=t_value+$value where sku='$sku' and size='$size'";
		$result = $db->query($sql);
		return $result;
	}

	/**减少实际库存*/
	public function minustnum($sku,$size,$value)
	{
		$db = $this->table->getAdapter();
		$sql = "update stock_value set t_value=t_value-$value where sku='$sku' and size='$size'";
		$result = $db->query($sql);
		return $result;
	}
}